package com.is.control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;

import com.is.util.Pagination;
import com.is.dbconn.DBConnect;
import com.is.model.Student;

public class StudentControl {
	public static Pagination pageInstance = null;
	public static boolean insertStudent(Student s){
		String sql = "insert into stu_info(id, passportName, chineseName, picPath, nationality, nationalityEn, sex, " +
		"birthday, classId, passportNumber, CSCNumber, scholarshipType, level, duration, university, " +
		"major, visaApplicationPath, admissionPath, passportPath, chineseVisaPath, type, tip) values (?, ?, ?, ?, ?, ?, ?, ?, ?, " + 
		"?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; 
		DBConnect dbc = new DBConnect();
		dbc.prepareStatement(sql);
		try{
			int i = 1;
			dbc.setInt(i++, s.getId());
			dbc.setString(i++, s.getPassportName());
			dbc.setString(i++, s.getChineseName());
			dbc.setString(i++, s.getPicPath());
			dbc.setString(i++, s.getNationality());
			dbc.setString(i++, s.getNationalityEn());
			dbc.setString(i++, s.getSex());
			dbc.setString(i++, s.getBirthday());
			dbc.setString(i++, s.getClassId());
			dbc.setString(i++, s.getPassportNumber());
			dbc.setString(i++, s.getCscNumber());
			dbc.setString(i++, s.getScholarshipType());
			dbc.setString(i++, s.getLevel());
			dbc.setString(i++, s.getDuration());
			dbc.setString(i++, s.getUniversity());
			dbc.setString(i++, s.getMajor());
			dbc.setString(i++, s.getVisaApplicationPath());
			dbc.setString(i++, s.getAdmissionPath());
			dbc.setString(i++, s.getPassportPath());
			dbc.setString(i++, s.getChineseVisaPath());
			dbc.setString(i++, s.getType());
			dbc.setString(i++, s.getTip());
			dbc.executeUpdate();
			return true;
		} catch(Exception e){
			e.printStackTrace();
			System.out.println("录入学生失败");
			return false;
		} finally {
			dbc.close();
		}
	}
	public static boolean delStudent(int id){
		DBConnect dbc = new DBConnect();
		String sql = "delete from stu_info where id =" + id;
		dbc.prepareStatement(sql);
		try{
			dbc.executeUpdate();
			return true;
		} catch(Exception e){
			e.printStackTrace();
			System.out.println("删除学生信息失败");
			return false;
		}
	}
	public static boolean updateStudent(Student s, int id){
		DBConnect dbc = new DBConnect();
		String sql = "update stu_info set passportName = ?, chineseName = ?, picPath = ?," +
		" nationality = ?, nationalityEn = ?, sex = ?, birthday = ?,  classId = ?," +
		" passportNumber = ?, CSCNumber = ?, scholarshipType = ?, level = ?, duration = ?," +
		" university = ?, major = ?, visaApplicationPath = ?, admissionPath = ?, passportPath = ?,"+
		" chineseVisaPath = ?, type = ?, tip = ? where id="+id;
		dbc.prepareStatement(sql);
		try{
			int i = 1;
			dbc.setString(i++, s.getPassportName());
			dbc.setString(i++, s.getChineseName());
			dbc.setString(i++, s.getPicPath());
			dbc.setString(i++, s.getNationality());
			dbc.setString(i++, s.getNationalityEn());
			dbc.setString(i++, s.getSex());
			dbc.setString(i++, s.getBirthday());
			dbc.setString(i++, s.getClassId());
			dbc.setString(i++, s.getPassportNumber());
			dbc.setString(i++, s.getCscNumber());
			dbc.setString(i++, s.getScholarshipType());
			dbc.setString(i++, s.getLevel());
			dbc.setString(i++, s.getDuration());
			dbc.setString(i++, s.getUniversity());
			dbc.setString(i++, s.getMajor());
			dbc.setString(i++, s.getVisaApplicationPath());
			dbc.setString(i++, s.getAdmissionPath());
			dbc.setString(i++, s.getPassportPath());
			dbc.setString(i++, s.getChineseVisaPath());
			dbc.setString(i++, s.getType());
			dbc.setString(i++, s.getTip());
			dbc.executeUpdate();
			return true;
		} catch(Exception e){
			e.printStackTrace();
			System.out.println("更新学生失败");
			return false;
		} finally{
			dbc.close();
		}
	}
	public static Student getStudent(int id){
		DBConnect dbc = new DBConnect();
		String sql = "select * from stu_info where id =" + id;
		dbc.prepareStatement(sql);
		ResultSet rs = null;
		Student s = new Student();
		try{
			rs = dbc.executeQuery();
			while(rs.next()){
				s.setId(id);
				s.setPassportName(rs.getString("passportName"));
				s.setChineseName(rs.getString("chineseName"));
				s.setPicPath(rs.getString("picPath"));
				s.setNationality(rs.getString("nationality"));
				s.setNationalityEn(rs.getString("nationalityEn"));
				s.setSex(rs.getString("sex"));
				s.setBirthday(rs.getString("birthday"));
				s.setClassId(rs.getString("classId"));
				s.setPassportNumber(rs.getString("passportNumber"));
				s.setCscNumber(rs.getString("CSCNumber"));
				s.setScholarshipType(rs.getString("scholarshipType"));
				s.setLevel(rs.getString("level"));
				s.setDuration(rs.getString("duration"));
				s.setUniversity(rs.getString("university"));
				s.setMajor(rs.getString("major"));
				s.setVisaApplicationPath(rs.getString("visaApplicationPath"));
				s.setAdmissionPath(rs.getString("admissionPath"));
				s.setPassportPath(rs.getString("passportPath"));
				s.setChineseVisaPath(rs.getString("chineseVisaPath"));
				s.setType(rs.getString("type"));
				s.setTip(rs.getString("tip"));
			}
			return s;
		} catch(Exception e){
			e.printStackTrace();
			System.out.println("通过id获取学生失败");
			return null;
		} finally{
			dbc.close();
		}
	} 
	
	/**
	 * 根据学生输入信息返回包含关键字信息的相关学生ID
	 * @param keyword
	 * @return
	 */
	public static List<Integer> getStudentIdByKeyword(String keyword){
		List<Integer> stuIds = new ArrayList<Integer>();
		DBConnect dbConnect = new DBConnect();
		String sql = "select id from stu_info where chineseName like '%"+keyword+"%' or passportName  like '%"+keyword+"%' or nationality  like '%"+keyword+"%'" +
				" or nationalityEn  like '%"+keyword+"%' ";
		dbConnect.prepareStatement(sql);
		ResultSet rSet = null;
		try {
			rSet = dbConnect.executeQuery();
			while(rSet.next()){
				stuIds.add(rSet.getInt("id"));
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			dbConnect.close();
		}
		return stuIds;
		
	}
	
	/**
	 * 获取每班人数
	 * @param classId
	 * @return
	 */
	public static int getCountByClassId(int classId) {
	    DBConnect dbc= new DBConnect();
	    String sql = "select count(id) from stu_info where classId ='"+classId+"'";
	    dbc.prepareStatement(sql);
	    ResultSet rSet = null;
	    try {
		rSet = dbc.executeQuery();
		if(rSet.next()){
		   return rSet.getInt(1);
		}
	    } catch (Exception e) {
		// TODO: handle exception
		e.printStackTrace();
		System.out.println("getCountByClassId ERROR");
	    }finally{
		dbc.close();
	    }
	    return 0;
      }
	public static int getCountTeacherByClassId(int classId) {
	    DBConnect dbc= new DBConnect();
	    String sql = "select count(id) from course_class where classId ='"+classId+"'";
	    dbc.prepareStatement(sql);
	    ResultSet rSet = null;
	    try {
		rSet = dbc.executeQuery();
		if(rSet.next()){
		   return rSet.getInt(1);
		}
	    } catch (Exception e) {
		// TODO: handle exception
		e.printStackTrace();
		System.out.println("getCountByClassId ERROR");
	    }finally{
		dbc.close();
	    }
	    return 0;
      }
	
	public static List<Student> getStudentByClassId(String classId){
		DBConnect dbc = new DBConnect();
		String sql = "select * from stu_info where classId ='" + classId + "'";
		List<Student> list = new ArrayList<Student>();
		ResultSet rs = null;
		dbc.prepareStatement(sql);
		try{
			rs = dbc.executeQuery();
			while(rs.next()){
				Student s = new Student();
				s.setId(rs.getInt("id"));
				s.setPassportName(rs.getString("passportName"));
				s.setChineseName(rs.getString("chineseName"));
				s.setPicPath(rs.getString("picPath"));
				s.setNationality(rs.getString("nationality"));
				s.setNationalityEn(rs.getString("nationalityEn"));
				s.setSex(rs.getString("sex"));
				s.setBirthday(rs.getString("birthday"));
				s.setClassId(rs.getString("classId"));
				s.setPassportNumber(rs.getString("passportNumber"));
				s.setCscNumber(rs.getString("CSCNumber"));
				s.setScholarshipType(rs.getString("scholarshipType"));
				s.setLevel(rs.getString("level"));
				s.setDuration(rs.getString("duration"));
				s.setUniversity(rs.getString("university"));
				s.setMajor(rs.getString("major"));
				s.setVisaApplicationPath(rs.getString("visaApplicationPath"));
				s.setAdmissionPath(rs.getString("admissionPath"));
				s.setPassportPath(rs.getString("passportPath"));
				s.setChineseVisaPath(rs.getString("chineseVisaPath"));
				s.setType(rs.getString("type"));
				s.setTip(rs.getString("tip"));
				list.add(s);
				s = null;
			}
			return list;
		} catch(Exception e){
			e.printStackTrace();
			System.out.println("调用getStudentByGrade函数失败");
			return null;
		} finally{
			dbc.close();
		}
	}
	
	/**
	 * 
	 * @param chineseName
	 * @param nationality
	 * @param sex
	 * @param classId
	 * @param csc
	 * @param level
	 * @param major
	 * @return
	 * @throws SQLException
	 * @throws Exception
	 */
	public static List<Student> getAllStudentMulti(String chineseName, String nationality, String sex,  String classId, String csc, String level, String major,String grade) throws SQLException,Exception{
		String temp = " where ";
		String clause = temp;
		if(clause.equals(temp) && chineseName != null && !chineseName.equals("")){
			clause += "chineseName like '%" + chineseName + "%'";
		}
		if(clause.equals(temp) && nationality != null && !nationality.equals("")){
			clause += "nationality like '%" + nationality + "%'";
		}
		if(!clause.equals(temp) && chineseName != null && !chineseName.equals("")){
			clause += " and chineseName like '%" + chineseName + "%'";
		}
		
		if(!clause.equals(temp) && nationality != null && !nationality.equals("")){
			clause += " and nationality like '%" + nationality + "%'";
		}
		
		if(clause.equals(temp) && sex != null && !sex.equals("")){
			clause += "sex like '%" + sex + "%'";
		}
		if(!clause.equals(temp) && sex != null && !sex.equals("")){
			clause += " and sex like '%" + sex + "%'";
		}
		
		if(clause.equals(temp) && classId != null && !classId.equals("")){
			clause += "classId like '" + classId + "'";
		}
		if(!clause.equals(temp) && classId != null && !classId.equals("")){
			clause += " and classId like '" + classId + "'";
		}
		if(clause.equals(temp) && csc != null && !csc.equals("")){
			clause += "CSCNumber like '%" + csc + "%'";
		}
		if(!clause.equals(temp) && csc != null && !csc.equals("")){
			clause += " and CSCNumber like '%" + csc + "%'";
		}
		if(clause.equals(temp) && level != null && !level.equals("")){
			clause += "level like '%" + level + "%'";
		}
		if(!clause.equals(temp) && level != null && !level.equals("")){
			clause += " and level like '%" + level + "%'";
		}
		if(clause.equals(temp) && major != null && !major.equals("")){
			clause += "major like '%" + major + "%'";
		}
		if(!clause.equals(temp) && major != null && !major.equals("")){
			clause += " and major like '%" + major + "%'";
		}
		if(clause.equals(temp)){
			clause = "";
		}
		if(clause.equals(temp) && grade != null && !grade.equals("")){
			clause += " and param_class.grade like '"+ grade +"' and stu_info.classId = param_class.id";
		}
		if(!clause.equals(temp) && grade != null && !grade.equals("")){
			clause += " and param_class.grade like '"+ grade +"' and stu_info.classId = param_class.id";
		}
		String sql = "";
		if(grade != null && !grade.equals("")){
			sql="select stu_info.* from stu_info, param_class" + clause;
		}else{
			sql = "select * from stu_info" +clause;
		}
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		dbc.prepareStatement(sql);
		List<Student> list = new ArrayList<Student>();
		try{
			rs = dbc.executeQuery();
			while(rs.next()){
				Student s = new Student();
				s.setId(rs.getInt("id"));
				s.setPassportName(rs.getString("passportName"));
				s.setChineseName(rs.getString("chineseName"));
				s.setPicPath(rs.getString("picPath"));
				s.setNationality(rs.getString("nationality"));
				s.setNationalityEn(rs.getString("nationalityEn"));
				s.setSex(rs.getString("sex"));
				s.setBirthday(rs.getString("birthday"));
				s.setClassId(rs.getString("classId"));
				s.setPassportNumber(rs.getString("passportNumber"));
				s.setCscNumber(rs.getString("CSCNumber"));
				s.setScholarshipType(rs.getString("scholarshipType"));
				s.setLevel(rs.getString("level"));
				s.setDuration(rs.getString("duration"));
				s.setUniversity(rs.getString("university"));
				s.setMajor(rs.getString("major"));
				s.setVisaApplicationPath(rs.getString("visaApplicationPath"));
				s.setAdmissionPath(rs.getString("admissionPath"));
				s.setPassportPath(rs.getString("passportPath"));
				s.setChineseVisaPath(rs.getString("chineseVisaPath"));
				s.setType(rs.getString("type"));
				s.setTip(rs.getString("tip"));
				list.add(s);
				s = null;
			}
			return list;
		} catch(Exception e){
			e.printStackTrace();
			System.out.println("调用getAllStudentMulti函数失败");
			return null;
		} finally{
			dbc.close();
		}
	}
	public static HashMap getClassName(String grade){
		DBConnect dbc = new DBConnect();
		String sql ="select * from param_class where grade = '" + grade +"'";
		dbc.prepareStatement(sql);
		HashMap map = new HashMap();
		ResultSet rs = null;
		try{
			rs = dbc.executeQuery();
			while(rs.next()){
				map.put(rs.getInt("id"), rs.getString("className"));
			}
			return map;
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			dbc.close();
		}
		return map;
	}
	public static HashMap getGradeYear(){
		DBConnect dbc = new DBConnect();
		String sql = "select DISTINCT grade from param_class ORDER BY id DESC";
		dbc.prepareStatement(sql);
		HashMap map = new HashMap();
		ResultSet rs = null; 
		try{
			rs = dbc.executeQuery();
			while(rs.next()){
				map.put(rs.getString("grade"),rs.getString("grade") + "级");
			}
			return map;
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			dbc.close();
		}
		return map;
	}
	public static Collection<Object> getStudentMulti(String chineseName, String nationality, String sex,  String classId, String csc, String level, String major, String grade, int page, int rowsPerPage) throws SQLException,Exception{
		String temp = " where ";
		String clause = temp;
		if(clause.equals(temp) && chineseName != null && !chineseName.equals("")){
			clause += "chineseName like '%" + chineseName + "%'";
		}
		if(clause.equals(temp) && nationality != null && !nationality.equals("")){
			clause += "nationality like '%" + nationality + "%'";
		}
		if(!clause.equals(temp) && chineseName != null && !chineseName.equals("")){
			clause += " and chineseName like '%" + chineseName + "%'";
		}
		if(!clause.equals(temp) && nationality != null && !nationality.equals("")){
			clause += " and nationality like '%" + nationality + "%'";
		}
		if(clause.equals(temp) && sex != null && !sex.equals("")){
			clause += "sex like '%" + sex + "%'";
		}
		if(!clause.equals(temp) && sex != null && !sex.equals("")){
			clause += " and sex like '%" + sex + "%'";
		}
		if(clause.equals(temp) && classId != null && !classId.equals("")){
			clause += "classId like '" + classId + "'";
		}
		if(!clause.equals(temp) && classId != null && !classId.equals("")){
			clause += " and classId like '" + classId + "'";
		}
		if(clause.equals(temp) && csc != null && !csc.equals("")){
			clause += "CSCNumber like '%" + csc + "%'";
		}
		if(!clause.equals(temp) && csc != null && !csc.equals("")){
			clause += " and CSCNumber like '%" + csc + "%'";
		}
		if(clause.equals(temp) && level != null && !level.equals("")){
			clause += "level like '%" + level + "%'";
		}
		if(!clause.equals(temp) && level != null && !level.equals("")){
			clause += " and level like '%" + level + "%'";
		}
		if(clause.equals(temp) && major != null && !major.equals("")){
			clause += "major like '%" + major + "%'";
		}
		if(!clause.equals(temp) && major != null && !major.equals("")){
			clause += " and major like '%" + major + "%'";
		}
		if(clause.equals(temp)){
			clause = "";
		}
		if(clause.equals(temp) && grade != null && !grade.equals("")){
			clause += " and param_class.grade like '"+ grade +"' and stu_info.classId = param_class.id";
		}
		if(!clause.equals(temp) && grade != null && !grade.equals("")){
			clause += " and param_class.grade like '"+ grade +"' and stu_info.classId = param_class.id";
		}
		String sql = "";
		if(grade != null && !grade.equals("")){
			sql="select stu_info.* from stu_info, param_class" + clause;
		}else{
			sql = "select * from stu_info" +clause;
		}
		pageInstance = new StudentControlPage(sql, rowsPerPage);
		try {
		     return pageInstance.getPage(page);
		}catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
}
class StudentControlPage extends Pagination{
	public StudentControlPage(String sql, int rowsPerPage){
		super(sql, rowsPerPage, 1);
	}
	public Collection<Student> packResultSet(ResultSet rs){
		Collection<Student> cs = new ArrayList<Student>();
		try{
			while(rs.next()){
				Student s = new Student();
				s.setId(rs.getInt("id"));
				s.setPassportName(rs.getString("passportName"));
				s.setChineseName(rs.getString("chineseName"));
				s.setPicPath(rs.getString("picPath"));
				s.setNationality(rs.getString("nationality"));
				s.setNationalityEn(rs.getString("nationalityEn"));
				s.setSex(rs.getString("sex"));
				s.setBirthday(rs.getString("birthday"));
				s.setClassId(rs.getString("classId"));
				s.setPassportNumber(rs.getString("passportNumber"));
				s.setCscNumber(rs.getString("CSCNumber"));
				s.setScholarshipType(rs.getString("scholarshipType"));
				s.setLevel(rs.getString("level"));
				s.setDuration(rs.getString("duration"));
				s.setUniversity(rs.getString("university"));
				s.setMajor(rs.getString("major"));
				s.setVisaApplicationPath(rs.getString("visaApplicationPath"));
				s.setAdmissionPath(rs.getString("admissionPath"));
				s.setPassportPath(rs.getString("passportPath"));
				s.setChineseVisaPath(rs.getString("chineseVisaPath"));
				s.setType(rs.getString("type"));
				s.setTip(rs.getString("tip"));
				cs.add(s);
				s=null;
			}
			return cs;
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("调用packResultSet失败");
			return null;
		}
 	}
}
